package EPM;

import com.taskadapter.redmineapi.*;
import com.taskadapter.redmineapi.bean.*;
import com.taskadapter.redmineapi.internal.Transport;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;

public class EPM2Redmine {
//    public static void main(String[] args) {
//        EPM2Redmine epm2Redmine = new EPM2Redmine();
//        try {
//            epm2Redmine.syncEPM2Redmine();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//    }

    public void syncEPM2Redmine() throws SQLException, RedmineException {
        int project_id = 461;// 项目(测试)
//        String project_id = "358";// 项目(医院现场需求及问题反馈)
        String is_private = "0";// 是否私有
        String parent_issue_id = "";//EPM没有父问题
        Map<String, String> status_map = new HashMap<>();
        status_map.put("0", "37");
        status_map.put("1", "38");
        status_map.put("2", "37");
        status_map.put("3", "38");
        status_map.put("4", "38");
        status_map.put("5", "38");
        status_map.put("6", "38");
        status_map.put("7", "39");
        status_map.put("8", "40");
        status_map.put("10", "38");
        status_map.put("11", "41");
        Map<String, Integer> priority_map = new HashMap<>();
        priority_map.put("0", 10);
        priority_map.put("1", 11);
        priority_map.put("2", 12);
        Map<String, String> tracker_map = new HashMap<>();
        tracker_map.put("0", "9");
        tracker_map.put("1", "10");
        tracker_map.put("4", "7");
        tracker_map.put("5", "8");

        RedmineManager redmineManager = RedmineManagerFactory.createWithApiKey("http://192.168.8.10:8001/", "45b5fc0e9fecba9fa2593360d83eafa0a79c3a66");
        Transport transport = redmineManager.getTransport();

        // 获取redmine和epm的id对应关系
        IssueManager issueManager = redmineManager.getIssueManager();
        List<Issue> results = null;
        try {
            results = issueManager.getIssues("461", null);
        } catch (RedmineException e) {
            e.printStackTrace();
        }
        HashMap<Integer, Integer> idMap = new HashMap<>();
        for (Issue result : results) {
            Integer redMineId = result.getId();
            String subject = result.getSubject();
            String[] split = subject.split("#@#");
            int epmId;
            if (split.length == 2) {
                epmId = Integer.parseInt(split[1]);
            } else {
                continue;
            }
            idMap.put(epmId, redMineId);
        }


        // 从redmine数据库中取到mail和id的映射
        Connection mysqlConn = DriverManager.getConnection("jdbc:mysql://192.168.8.10:3306/redmine_409", "redmine", "rm@jhhs_2022");
        PreparedStatement sql = mysqlConn.prepareStatement("select user_id,address from email_addresses");
        sql.setFetchSize(300);
        ResultSet sqlResultSet = sql.executeQuery();

        // (emailName, redmine_user_id)
        HashMap<String, Integer> userMap = new HashMap<>();
        try {
            while (sqlResultSet.next()) {
                String email = sqlResultSet.getString("address");
                String emailName = email.substring(0, email.indexOf("@"));
                int user_id = sqlResultSet.getInt("user_id");
                userMap.put(emailName, user_id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                mysqlConn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        // 获取项目成员
        ProjectManager projectManager = redmineManager.getProjectManager();
        List<Membership> projectMembers = projectManager.getProjectMembers(461);
        List<Integer> memIds = projectMembers.stream().map(Membership::getUserId).collect(Collectors.toList());

        // 过滤只要项目成员的问题
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.20:1521/EPM", "redmineviewer", "redmineviewer");
        PreparedStatement sta = conn.prepareStatement("SELECT t1.*, t2.NAME AS SUBMIT_USER_NAME FROM ( SELECT evp.ID, evp.TITLE, evp.KIND, evp.DESCRIPTION, evp.STATUS, evp.PRIORITY, evp.CREATED_AT, evp.PLAN_DATE, evp.PLAN_TIME, evu.MAIL, evu.NAME, evp.SUBMIT_USER FROM epmuser2.epm_view_problem evp LEFT JOIN EPMUSER2.EPM_VIEW_USER evu ON evp.PLAN_USER = evu.ID WHERE evp.STATUS = 2 OR evp.STATUS = 3 ) t1 LEFT JOIN EPMUSER2.EPM_VIEW_USER t2 ON t1.SUBMIT_USER = t2.ID");
        sta.setFetchSize(300);
        ResultSet resultSet = sta.executeQuery();

        try {
            while (resultSet.next()) {
                // EPM读到的字段
                int id = resultSet.getInt("ID");
                // 如果在redmine中已存在此问题，跳过
                if (idMap.containsKey(id)) {
                    System.out.println("redmine中已存在此问题，跳过");
                    continue;
                }
                String title = resultSet.getString("TITLE");
                String mail = resultSet.getString("MAIL");
                String mailName = "";
                if (mail != null){
                    mailName = mail.substring(0, mail.indexOf("@"));
                }
                String usrName = resultSet.getString("NAME");
                String kind = resultSet.getString("KIND");
                String descript = resultSet.getString("DESCRIPTION").replaceAll("chr\\(13\\)", "\n");
                String status = resultSet.getString("STATUS");
                String priority = resultSet.getString("PRIORITY");
                String created_at = resultSet.getString("CREATED_AT");
                String plan_date = resultSet.getString("PLAN_DATE");
                String plan_time = resultSet.getString("PLAN_TIME");
                String submit_user_name = resultSet.getString("SUBMIT_USER_NAME");
                descript = "(创建人：" + submit_user_name + ")\n" + descript;

                // redmine所需的字段
                int tracker_id = Integer.parseInt(tracker_map.getOrDefault(kind, "0"));// 跟踪（类型）
                int status_id = Integer.parseInt(status_map.getOrDefault(status, "0"));// 状态
                // 根据email获取指定员工
                int userId = userMap.getOrDefault(mailName, 0);
                // 判断项目中是否有该员工
//                if (userId != 0 && !memIds.contains(userId)){
                if (!memIds.contains(userId)){
                    System.out.println("该员工不在项目成员中,跳过");
                    continue;
                }

                int priority_id = priority_map.getOrDefault(priority, 10);// 优先级
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date create_date = new Date();// 创建时间
                Date start_date = dateFormat.parse(created_at);// 开始时间
                Date due_date = null;// 预计完成时间
                if (plan_date != null) {
                    due_date = dateFormat.parse(plan_date);
                }
                float estimatedHours = 0;// 预计工时
                if (plan_time != null) {
                    estimatedHours = Float.parseFloat(plan_time);
                }
                int done_ratio = 0;// 进度

                createIssue(transport, id, project_id, title, tracker_id, status_id, priority_id, userId, usrName, descript, create_date, start_date, due_date, estimatedHours, done_ratio,idMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        // 更新附件
        AttachmentManager attachmentManager = redmineManager.getAttachmentManager();
        List<Integer> EPMId = new ArrayList<>(idMap.keySet());
        List<Integer> redmineId = new ArrayList<>(idMap.values());

        StringBuilder sb = new StringBuilder();
        sb.append(0);
        EPMId.forEach(id -> sb.append(",").append(id));
        Connection oracle = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.20:1521/EPM", "redmineviewer", "redmineviewer");
        PreparedStatement statement = oracle.prepareStatement("SELECT evp.ID,epf.CONTENT,epf.FILE_NAME FROM epmuser2.EPM_PROBLEM_FILE epf LEFT JOIN epmuser2.EPM_VIEW_PROBLEM evp ON epf.PROBLEM_ID = evp.id WHERE evp.ID IN (" + sb + ")");
        statement.setFetchSize(300);
        ResultSet result = statement.executeQuery();

        try {
            while (result.next()) {
                int id = result.getInt("ID");
                Blob content = result.getBlob("CONTENT");
                String file_name = result.getString("FILE_NAME");

                Integer issueId = idMap.get(id);
                Issue issue = issueManager.getIssueById(issueId,Include.attachments);
                List<String> attachmentNames = issue.getAttachments().stream().map(Attachment::getFileName).collect(Collectors.toList());
                if (attachmentNames.contains(file_name)){
                    System.out.println("issueId：" + issueId + ",已存在附件：" + file_name + ",跳过...");
                    continue;
                }
                // 添加附件
                InputStream binaryStream = content.getBinaryStream();
                File file = new File("D:\\test\\" + file_name);

                try (FileOutputStream outputStream = new FileOutputStream(file)) {
                    int read;
                    byte[] bytes = new byte[1024];
                    while ((read = binaryStream.read(bytes)) != -1) {
                        outputStream.write(bytes, 0, read);
                    }
                }
                binaryStream.close();
                attachmentManager.addAttachmentToIssue(issueId,file,"");
                System.out.println("issueId：" + issueId + ",成功添加附件：" + file_name);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                oracle.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private void createIssue(Transport transport, int issueId, int projectId, String subject, int trackerId, int statusId, int priorityId, int assigneeId, String usrName, String desc, Date createdOn, Date startDate, Date dueDate, float estimatedHours, Integer doneRatio,HashMap<Integer,Integer> idMap) {
        try {
            Issue issue = new Issue(transport, projectId, subject + "#@#" + issueId);
            issue.setTracker(new Tracker().setId(10));
            issue.setStatusId(statusId);
            issue.setPriorityId(priorityId);
            if (assigneeId != 0) {
                issue.setAssigneeId(assigneeId);
            }
            issue.setDescription(desc);
            issue.setCreatedOn(createdOn);
            issue.setStartDate(startDate);
            issue.setDueDate(dueDate);
            issue.setEstimatedHours(estimatedHours);
            issue.setDoneRatio(doneRatio);
            issue.addCustomField(CustomFieldFactory.create(6, "开发人员", usrName));
            issue.addCustomField(CustomFieldFactory.create(7, "缺陷类型", "7"));
            issue.addCustomField(CustomFieldFactory.create(24, "任务类型", "开发任务"));

            Issue createdIssue = issue.create();
            Integer newId = createdIssue.getId();
            System.out.println("创建成功,issue id:" + newId);
            idMap.put(issueId,newId);
        } catch (RedmineException e) {
            e.printStackTrace();
        }
    }


}
